<?php

/**
 * Created by PhpStorm.
 * User: zuoa
 * Date: 2016/12/16 0016
 * Time: 14:08
 */
class Violation_model extends Y_Model
{
    private $table_nam = "view_violation_record_detail";

    public function add_record($r)
    {
        return $this->db->insert("tbl_violation_record", $r);
    }

    public function get_records_by_violator($idcard)
    {
        $query = $this->db->where("violator_idcard", $idcard)->order_by("ts DESC")->get("view_violation_record_detail");
        return $query->result_array();
    }

    public function get_total_count($begin, $end, $dept = '')
    {
        if (empty($dept)) {
            return $this->db->where(array("ts >" => $begin, "ts <" => $end))->count_all_results($this->table_nam);
        } else {
            return $this->db->where(array("ts >" => $begin, "ts <" => $end, "dept" => $dept))->count_all_results($this->table_nam);
        }
    }

    public function get_violation_ranks($begin, $end, $num = 5, $dept = '')
    {
        $this->db->select('violation_code, violation_desc, COUNT(id) as count');
        $this->db->from($this->table_nam);
        $this->db->where(array("ts >" => $begin, "ts <" => $end));
        $this->db->group_by("violation_code");
        $this->db->limit($num);
        $this->db->order_by("count DESC");
        if (!empty($dept)) {
            $this->db->where("dept", $dept);
        }

        $query = $this->db->get();

        return $query->result_array();
    }

    public function get_department_ranks($begin, $end,  $num = 5)
    {
        $this->db->select('dept, COUNT(id) as count');
        $this->db->from($this->table_nam);
        $this->db->where("dept IS NOT null");
        $this->db->where(array("ts >" => $begin, "ts <" => $end));
        $this->db->group_by("dept");
        $this->db->order_by("count DESC");
        $query = $this->db->get();

        return $query->result_array();
    }

    public function get_police_ranks($begin, $end, $num = 5, $dept = '')
    {
        $this->db->select('name, COUNT(id) as count');
        $this->db->from($this->table_nam);
        $this->db->where("name IS NOT null");
        $this->db->where(array("ts >" => $begin, "ts <" => $end));
        $this->db->group_by("name");
        $this->db->order_by("count DESC");

        if (!empty($dept)) {
            $this->db->where("dept", $dept);
        }

        $query = $this->db->get();

        return $query->result_array();
    }

    public function get_violator_ranks($begin, $end, $num = 5)
    {
        $this->db->select('violator_idcard as idcard, COUNT(id) as count');
        $this->db->from('tbl_violation_record');
        $this->db->where("violator_idcard != ''");
        $this->db->where(array("ts >" => $begin, "ts <" => $end));
        $this->db->group_by("idcard");
        $this->db->order_by("count DESC");
        $query = $this->db->get();

        return $query->result_array();
    }

    /**
     * @param  $dept ：单位名称，默认为空，显示所有数据
     * @param $num ： 0返回本月数据，大于0显示近num天数据
     */
    public function get_statistics_by_day($dept = '', $num = 0)
    {

        $this->db->select("DATE_FORMAT(ts,'%Y-%m-%d') as day, COUNT(id) as count");
        $this->db->from('view_violation_record_detail');
        $this->db->group_by("day");
        $this->db->order_by("day DESC");

        if ($num == 0) {
            $this->db->where("DATE_FORMAT(ts, '%Y-%m')=DATE_FORMAT(now(), '%Y-%m')");

            if ($dept != '') {
                $this->db->where("dept", $dept);
            }
        } else {
            $this->db->limit($num);

            if ($dept != '') {
                $this->db->where("dept", $dept);
            }
        }

        $query = $this->db->get();

        return $query->result_array();
    }

    public function get_export_result()
    {
        $this->load->dbutil();

        $this->db->select("CONCAT(\"\t\", violator_idcard) as 违法人身份证号, violator_name as 违法人姓名, violator_tel as 违法人电话, ts as 违法时间, violation_code as 违法编码, violation_desc as 违法行为, name as 处理警员, dept as 处理单位");
        $query = $this->db->get("view_violation_record_detail");

        return $this->dbutil->csv_from_result($query);
    }

    public function  get_code()
    {
        $query = $this->db->get('tbl_violation_dict');
        return $query->result_array();
    }

    public function  get_list()
    {
        $query = $this->db->get('view_violation_record_detail');
        return $query->result_array();
    }


}